test.examples_unique_constraint source code

1 /**
2 The following examples come from
3 $(LINK http://zetcode.com/db/sqlite/constraints/).
4 Even though it is a SQLite tutorial the point is to show how to use this package
5 which does not have to be just SQLite.
6  */
7 module test.examples_unique_constraint;
8 
9 version(D_Ddoc)
10 {
11     ///
12     class BlankClassSoDocsWillBeGenerated { }
13 }
14 
15 
16 /**
17 This example is for the UNIQUE constraint and the PRIMARY KEY constraint.
18 The table in SQL can be created by
19 $(D $(D $(D sql
20 CREATE TABLE Brands
21 (
22     Id INTEGER NOT NULL PRIMARY KEY,
23     BrandName TEXT UNIQUE
24 );
25 
26 )))
27 
28 Any column marked with @PrimaryKeyColumn must also have @NotNull. Unlike SQLite,
29 if a column is marked as int and @PrimaryKeyColumn it is not auto-incremented.
30  */
31 unittest
32 {
33     import db_constraints;
34 
35     class Brand
36     {
37         private int _Id;
38         // marking Id with not null and primary key
39         @NotNull @PrimaryKeyColumn
40         @property int Id()
41         {
42             return _Id;
43         }
44         @property void Id(int value)
45         {
46             setter(_Id, value);
47         }
48 
49         private string _BrandName;
50         // marking BrandName with UniqueConstraintColumn
51         // so the collection will know this property should be
52         // unique for all records
53         @UniqueConstraintColumn!("Unique")
54         @property string BrandName()
55         {
56             return _BrandName;
57         }
58         @property void BrandName(string value)
59         {
60             setter(_BrandName, value);
61         }
62 
63         this(int Id_, string BrandName_)
64         {
65             this._Id = Id_;
66             this._BrandName = BrandName_;
67             // do not forget to initialize the keyed item!
68             initializeKeyedItem();
69         }
70 
71         // do not forget to add in the keyed item!
72         mixin KeyedItem!();
73     }
74 
75     // this is what I call the plural class
76     // or table class. This is the collection
77     // of rows (in this example Brands).
78     class Brands
79     {
80         // this mixin already does the
81         // initializations and every method
82         // I want for this tutorial so I
83         // do not need anything else.
84         mixin KeyedCollection!(Brand);
85     }
86 
87     import std.exception : assertNotThrown, assertThrown;
88 
89     // UNIQUE constraint
90     {
91         // we can start by putting two records into the collection.
92         // now brands holds a record for Coca Cola and Pepsi
93         auto brands = new Brands([new Brand(1, "Coca Cola"),
94                                   new Brand(2, "Pepsi")]);
95 
96         auto anotherPepsi = new Brand(3, "Pepsi");
97         // if we try to add another record that has Pepsi for
98         // the brand name we will get a unique constraint exception
99         assertThrown!UniqueConstraintException(brands.add(anotherPepsi));
100 
101         // we can see if the new record will violate any unique constraints
102         // before we add it to the collection by using violatesUniqueConstraints
103         assert(brands.violatesUniqueConstraints(anotherPepsi));
104     }
105 
106     // PRIMARY KEY constraint
107     {
108         // the primary key is unique and not null
109         // by default we can use the primary key to look up
110         // records in the collection
111         auto brands = new Brands([new Brand(1, "Coca Cola"),
112                                   new Brand(2, "Pepsi")]);
113 
114         // since Pepsi's Id is the primary key we can use 2 to find pepsi
115         assert(brands[2].BrandName == "Pepsi");
116         assert(brands[2].Id == 2);
117         // this is because brands is really an associative array that uses the
118         // primary key in this case as the AA key. We can check if the
119         // collection already contains a primary key of 2 by using contains
120         assert(brands.contains(2));
121         // and does not contain 4
122         assert(!brands.contains(4));
123         // if you try to get an item that is not there you will get an exception
124         assertThrown!KeyedException(brands[4].BrandName);
125 
126         // lets add two more records
127         brands ~= [new Brand(3, "Sun"), new Brand(4, "Oracle")];
128 
129         // now it does contain 4
130         assert(brands.contains(4));
131         assertNotThrown!KeyedException(brands[4].BrandName);
132     }
133 }